﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Xml;
using System.Text.RegularExpressions;
using Data.Abstract;
using Data.Configuration;
using System.Data.SQLite;
namespace Data
{
    public class DalPosicion
    {
        #region Propiedades
        Context _Context = new Context();
        SQLiteConnection _Conn;
        #endregion
        #region Procedimientos
        public DalPosicion() { }
        public DalPosicion(SQLiteConnection conn)
        {
            this._Conn = conn;
        }
        #endregion
        #region Metodos
       
        public void Insert(SimplePosicion simple)
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                if (!CheckTableExist())
                {
                    CreateTable();
                }
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO Posicion (Calle,Numero,Depto,TerrNum,Lat,Long,Nota,IdPersona)values(@Calle,@Numero,@Depto,@TerrNum,@Lat,@Long,@Nota,@IdPersona)";

                cmd.Parameters.Add("@Id", DbType.Int32);
                cmd.Parameters.Add("@Calle", DbType.String);
                cmd.Parameters.Add("@Numero", DbType.Int32);
                cmd.Parameters.Add("@Depto", DbType.String);
                cmd.Parameters.Add("@TerrNum", DbType.Int32);
                cmd.Parameters.Add("@Lat", DbType.Decimal);
                cmd.Parameters.Add("@Long", DbType.Decimal);
                cmd.Parameters.Add("@Nota", DbType.String);
                cmd.Parameters.Add("@IdPersona", DbType.Int32);

                cmd.Parameters[0].Value = simple.Id;
                cmd.Parameters[1].Value = simple.Calle;
                cmd.Parameters[2].Value = simple.Numero;
                cmd.Parameters[3].Value = simple.Depto;
                cmd.Parameters[4].Value = simple.TerrNum;
                cmd.Parameters[5].Value = simple.Lat;
                cmd.Parameters[6].Value = simple.Long;
                cmd.Parameters[7].Value = simple.Nota;
                cmd.Parameters[8].Value = simple.IdPersona;

                conn.Open();
                cmd.ExecuteNonQuery();

            }

        }
        public void Update(SimplePosicion simple)
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                if (CheckTableExist())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Update Posicion set Calle = @Calle,Numero = @Numero,Depto = @Depto,TerrNum = @TerrNum,Lat = @Lat,Long = @Long,Nota = @Nota,IdPersona = @IdPersona where Id = @Id";


                    cmd.Parameters.Add("@Id", DbType.Int32);
                    cmd.Parameters.Add("@Calle", DbType.String);
                    cmd.Parameters.Add("@Numero", DbType.Int32);
                    cmd.Parameters.Add("@Depto", DbType.String);
                    cmd.Parameters.Add("@TerrNum", DbType.Int32);
                    cmd.Parameters.Add("@Lat", DbType.Decimal);
                    cmd.Parameters.Add("@Long", DbType.Decimal);
                    cmd.Parameters.Add("@Nota", DbType.String);
                    cmd.Parameters.Add("@IdPersona", DbType.Int32);

                    cmd.Parameters[0].Value = simple.Id;
                    cmd.Parameters[1].Value = simple.Calle;
                    cmd.Parameters[2].Value = simple.Numero;
                    cmd.Parameters[3].Value = simple.Depto;
                    cmd.Parameters[4].Value = simple.TerrNum;
                    cmd.Parameters[5].Value = simple.Lat;
                    cmd.Parameters[6].Value = simple.Long;
                    cmd.Parameters[7].Value = simple.Nota;
                    cmd.Parameters[8].Value = simple.IdPersona;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void CreateTable()
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "CREATE TABLE Posicion (Id INTEGER PRIMARY KEY AUTOINCREMENT,  Fecha datetime NOT NULL, NewsId int NOT NULL, Posicions int NOT NULL)";
                conn.Open();
                cmd.ExecuteNonQuery();
            }


        }
        public DataTable GetByPersonaId(int IdPersona)
        {
            DataTable Out = new DataTable();
            if (CheckTableExist())
            {

                using (SQLiteConnection conn = this._Conn)
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Select * from Posicion where IdPersona = @IdPersona ";

                    cmd.Parameters.Add("@IdPersona", DbType.DateTime);
                    cmd.Parameters[0].Value = IdPersona;

                    conn.Open();
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                    sda.Fill(Out);

                }
            }
            return Out;
        }
        public DataTable GetAll()
        {
            DataTable Out = new DataTable();

            if (CheckTableExist())
            {
                using (SQLiteConnection conn = this._Conn)
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Select * from Posicion";
                    conn.Open();
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                    sda.Fill(Out);
                }
            }
            return Out;
        }
        public bool CheckTableExist()
        {

            DataTable Out = new DataTable();
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = " SELECT * FROM sqlite_master WHERE name = 'Posicion' AND type = 'table'";
                conn.Open();
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                sda.Fill(Out);
                bool SchemaOk = (Out.Rows.Count == 0) ? false : true;
                return SchemaOk;
            }
        }

        #endregion
    }
}